#
#用于从盘点数据表中生产数据到未提交的ICBill
#
#需要在.bahs_profile设置环境变量
#export PSI_PASSWORD=xxxxxxx
#export RDS_AGENT=yyyy
#
#1)将psi.production_warehouse_inventory导出excel交给盘点负责人
#2)在表最后一列stock_count填入实际盘点数量
#3)将该表要导入ogoods.psi_icbill_data
#4)手工在psi系统建立一张空的盘点单
#5)手工在命令行运行 ruby gen_icbill2.rb
#5)回psi系统手工提交盘点单
#
require 'mysql2'

    rds = Mysql2::Client.new(:host => ENV['RDS_AGENT'], :username => "psi_root", :port => '1401', :password => ENV['PSI_PASSWORD'])

    priceHashP = {}; #Price hash in PSI goods_id
    priceHashM = {}; #Price hash in Magento code
    codeToGoodsID = {}; #Goode code to ID

    #prepare price reference table
    sql1 = 'select * from psi.t_goods'
    res1 = rds.query(sql1)
    res1.each do |tgr|
        code = tgr['code']
        id = tgr['id']
        name = tgr['name']
        purchase_price = tgr['purchase_price'];
        priceHashP.store(id,purchase_price);
        priceHashM.store(code,purchase_price);
        codeToGoodsID.store(code,id);
    end

    #get icbill_id
    sqli = "select * from psi.t_ic_bill where bill_status=0 and warehouse_id = '7E6C8D0D-E25A-11E8-8B92-6C92BF475963' limit 0, 1"
    resi = rds.query(sqli)
    if resi && resi.first then
            icbill_id = resi.first['id']
    else
            puts 'Error: please create an ic bill first'
            exit
    end

    #prepare unrecorded icbill_details 
    sqla = 'select * from ogoods.psi_icbill_data where imported_at is null';
    resa = rds.query(sqla)
    resa.each do |r|
            pid_id = r['id']
            goods_code = r['code']
            goods_name = r['name']
            stock_count = r['stock_count'] ? r['stock_count'] : 0
            goods_price = priceHashM[goods_code]
            goods_id = codeToGoodsID[goods_code]
            if ( stock_count && goods_price ) then

                    #insert icbill details
                    goods_money = goods_price * stock_count
                    sqlu = "insert into psi.t_ic_bill_detail(
                                id, date_created, 
                                goods_id, goods_count, goods_money,
                                icbill_id, 
                                show_order, data_org, company_id
                            ) values( 
                                UPPER(UUID()), CURRENT_TIMESTAMP(), 
                                '#{goods_id}',#{sprintf('%.8f',stock_count)},#{sprintf('%.2f',goods_money)},
                                '#{icbill_id}',  #ic_bill_id
                                0,'01010001','4D74E1E4-A129-11E4-9B6A-782BCBD7746B'
                            );"
                    #puts sqlu
                    resu = rds.query(sqlu)

                    #set imported flag
                    sqlu="update ogoods.psi_icbill_data set imported_at = CURRENT_TIMESTAMP(), stock_count=#{sprintf('%.8f',stock_count)} where id=#{pid_id}"
                    #puts sqlu
                    resu = rds.query(sqlu)
            else
                    puts "Error: #{goods_code} has no stock_count or purchase_price, skipped"
            end
    end

rds.close
